﻿CREATE SCHEMA pca_courier;

------------------------------- Configuraciones generales del sistema -------------------------------------

CREATE TABLE pca_courier.config
(
	oficina_id varchar(2) NOT NULL,
	empresa_id varchar(2) NOT NULL,
	clave varchar(50) NOT NULL,
	valor text NOT NULL,
	CONSTRAINT config_pk PRIMARY KEY(oficina_id,empresa_id,clave),
	CONSTRAINT oficinas_fkey FOREIGN KEY(oficina_id,empresa_id)
		REFERENCES  pca.oficinas(oficina_id,empresa_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT 
);

CREATE OR REPLACE FUNCTION pca_courier.fn_update_config(varchar,--oficina_id $1
							varchar,--empresa_id $2
							varchar, --clave $3
							text --valor $4
							)RETURNS void AS
$$
BEGIN
	UPDATE pca_courier.config SET
		valor = $2
	WHERE
		oficina_id = $1 AND
		empresa_id = $2 AND
		clave = $3;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_config(varchar,--oficina_id $1
						     varchar,--empresa_id $2
						     varchar --clave $3
						     )RETURNS text AS
$$
DECLARE
	_valor pca_courier.config.valor%TYPE;
BEGIN
	SELECT
		valor
	INTO
		_valor
	FROM
		pca_courier.config
	WHERE
		oficina_id = $1 AND
		empresa_id = $2 AND
		clave = $3;
		
	IF(FOUND)THEN
		RETURN _valor;
	ELSE
		RAISE EXCEPTION 'No existe configuración asignada para %',$1;
	END IF;
END;
$$
LANGUAGE plpgsql;

-------------------------------------------------------------------------------------------------------------

CREATE TABLE pca_courier.estado_orden_servicio
(
	estado_orden_servicio_id varchar(2) NOT NULL,
	nombre varchar(50) NOT NULL,
	CONSTRAINT estado_orden_servicio_pk PRIMARY KEY(estado_orden_servicio_id)
);

--En este estado la orden de servicio es editable(cuerpo,detalles,consignados etc)
INSERT INTO pca_courier.estado_orden_servicio VALUES ('00','RECEPCION');
--En este estado la orden de servicio paso a operaciones y no se permiten modificaciones
INSERT INTO pca_courier.estado_orden_servicio VALUES ('01','OPERACIONES');
--La orden de servicio ya fue atendida y no se permiten modificadion alguna
INSERT INTO pca_courier.estado_orden_servicio VALUES ('02','FINALIZADO');
--La orden de servicio fue anulada y no se permiten modificacion alguna
INSERT INTO pca_courier.estado_orden_servicio VALUES ('99','ANULADO');

-------------------------------------------------------------------------------------------------------------

CREATE TABLE pca_courier.tipo_destino
(
	tipo_destino_id varchar(2) NOT NULL,
	nombre varchar(50) NOT NULL,
	CONSTRAINT tipo_destino_pk PRIMARY KEY(tipo_destino_id)
);

INSERT INTO pca_courier.tipo_destino VALUES('00','LOCAL');
INSERT INTO pca_courier.tipo_destino VALUES('01','NACIONAL');
INSERT INTO pca_courier.tipo_destino VALUES('02','INTERNACIONAL');

CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_tipo_destino()RETURNS SETOF pca_courier.tipo_destino AS
$$
DECLARE
	_row pca_courier.tipo_destino%ROWTYPE;
BEGIN
	FOR _row IN
		SELECT
			tipo_destino_id,
			nombre
		FROM
			pca_courier.tipo_destino
	LOOP
		RETURN NEXT _row;
	END LOOP;
END;
$$
LANGUAGE plpgsql;

-------------------------------------------------------------------------------------------------------------

CREATE TABLE pca_courier.tipo_destino_oficina
(
	oficina_id varchar(2) NOT NULL,
	empresa_id varchar(2) NOT NULL,
	tipo_destino_id varchar(2) NOT NULL,
	CONSTRAINT tipo_destino_oficina_pk PRIMARY KEY(oficina_id,empresa_id,tipo_destino_id),
	CONSTRAINT tipo_destino_fkey FOREIGN KEY(tipo_destino_id)
		REFERENCES pca_courier.tipo_destino(tipo_destino_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT oficinas_fkey FOREIGN KEY(oficina_id,empresa_id)
		REFERENCES pca.oficinas(oficina_id,empresa_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT
);

CREATE OR REPLACE VIEW pca_courier.vw_tipo_destino_oficina AS
SELECT
	vt.oficina_id,
	vt.empresa_id,
	vt.tipo_destino_id,
	t.nombre
FROM
	pca_courier.tipo_destino_oficina vt
INNER JOIN
	pca_courier.tipo_destino t
ON
	(vt.tipo_destino_id=t.tipo_destino_id);

CREATE OR REPLACE FUNCTION pca_courier.fn_add_tipo_destino_oficina(varchar,--oficina_id $1
								   varchar,--empresa_id $2
								   varchar --tipo_destino_id $3
								   )RETURNS void AS
$$
BEGIN
	INSERT INTO 
		pca_courier.tipo_destino_oficina
	VALUES
		($1,$2,$3);
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_del_tipo_destino_oficina(varchar,--oficina_id $1
								   varchar,--empresa_id $2
								   varchar --tipo_destino_id $3
								   )RETURNS void AS
$$
BEGIN
	DELETE FROM
		pca_courier.tipo_destino_oficina
	WHERE
		oficina_id = $1 AND
		empresa_id = $2 AND
		tipo_destino_id = $3;
END;
$$
LANGUAGE plpgsql;					   

CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_tipo_destino_oficina(varchar,--oficina_id $1
									varchar --empresa_id $2
									)RETURNS SETOF pca_courier.vw_tipo_destino_oficina AS
$$
DECLARE
	_row pca_courier.vw_tipo_destino_oficina%ROWTYPE;
BEGIN
	FOR _row IN
		SELECT
			oficina_id,
			empresa_id,
			tipo_destino_id,
			nombre
		FROM
			pca_courier.vw_tipo_destino_oficina
		WHERE
			oficina_id = $1 AND
			empresa_id = $2
	LOOP
		RETURN NEXT _row;
	END LOOP;
END;
$$
LANGUAGE plpgsql;

-------------------------------------------------------------------------------------------------------------

CREATE TABLE pca_courier.tipo_servicio
(
	tipo_servicio_id varchar(2) NOT NULL,
	nombre varchar(50) NOT NULL,
	dias_minimo integer NOT NULL,--la cantidad de dias minimo en la que se entrega el envio
	dias_maximo integer NOT NULL,--la cantidad de dias maximo en la que se entrega el envio
	CONSTRAINT tipo_servicio_pk PRIMARY KEY(tipo_servicio_id)
);

INSERT INTO pca_courier.tipo_servicio VALUES('01','EXPRESS',1,1);
INSERT INTO pca_courier.tipo_servicio VALUES('02','PREMIUM',2,3);
INSERT INTO pca_courier.tipo_servicio VALUES('03','STANDAR',4,5);

CREATE OR REPLACE VIEW pca_courier.vw_tipo_servicio AS
SELECT
	tipo_servicio_id,
	CASE WHEN dias_minimo=0 AND dias_maximo=0 THEN nombre
	     WHEN dias_minimo=dias_maximo THEN nombre || ' ('|| dias_minimo || ' DIA)'
	     ELSE nombre || ' ('|| dias_minimo || ' A '||dias_maximo || ' DIAS)'
	END::varchar AS nombre,
	dias_minimo,
	dias_maximo
FROM
	pca_courier.tipo_servicio;
				       
CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_tipo_servicio()RETURNS SETOF pca_courier.vw_tipo_servicio AS
$$
DECLARE
	_row pca_courier.vw_tipo_servicio%ROWTYPE;
BEGIN
	FOR _row IN 
		SELECT
			tipo_servicio_id,
			nombre,
			dias_minimo,
			dias_maximo
		FROM
			pca_courier.vw_tipo_servicio
		ORDER BY
			tipo_servicio_id ASC
	LOOP
		RETURN NEXT _row;
	END LOOP;
END;
$$
LANGUAGE plpgsql;

-------------------------------------------------------------------------------------------------------------

CREATE TABLE pca_courier.tipo_servicio_oficina
(
	oficina_id varchar(2) NOT NULL,
	empresa_id varchar(2) NOT NULL,
	tipo_servicio_id varchar(2) NOT NULL,
	CONSTRAINT tipo_servicio_oficina_pk PRIMARY KEY(oficina_id,empresa_id,tipo_servicio_id),
	CONSTRAINT tipo_servicio_fkey FOREIGN KEY(tipo_servicio_id)
		REFERENCES pca_courier.tipo_servicio(tipo_servicio_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT oficinas_fkey FOREIGN KEY(oficina_id,empresa_id)
		REFERENCES pca.oficinas(oficina_id,empresa_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT
);

CREATE OR REPLACE VIEW pca_courier.vw_tipo_servicio_oficina AS
SELECT
	ts.oficina_id,
	ts.empresa_id,
	ts.tipo_servicio_id,
	CASE WHEN t.dias_minimo=0 AND t.dias_maximo=0 THEN nombre
	     WHEN t.dias_minimo=t.dias_maximo THEN nombre || ' ('|| t.dias_minimo || ' DIA)'
	     ELSE t.nombre || ' ('|| t.dias_minimo || ' A '|| t.dias_maximo || ' DIAS)'
	END::varchar AS nombre,
	t.dias_minimo,
	t.dias_maximo
FROM
	pca_courier.tipo_servicio_oficina ts
INNER JOIN
	pca_courier.tipo_servicio t
ON
	(ts.tipo_servicio_id=t.tipo_servicio_id);


CREATE OR REPLACE FUNCTION pca_courier.fn_add_tipo_servicio_oficina(varchar,--oficina_id $1
								    varchar,--empresa_id $2
								    varchar --tipo_servicio_id $3
								    )RETURNS void AS
$$
BEGIN
	INSERT INTO 
		pca_courier.tipo_servicio_oficina
	VALUES
		($1,$2,$3);
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_del_tipo_servicio_oficina(varchar,--oficina_id $1
								    varchar,--empresa_id $2
								    varchar --tipo_servicio_id $3
								    )RETURNS void AS
$$
BEGIN
	DELETE FROM 
		pca_courier.tipo_servicio_oficina
	WHERE
		oficina_id = $1 AND
		empresa_id = $2 AND
		tipo_servicio_id = $3;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_tipo_servicio_oficina(varchar,--oficina_id $1
									varchar --empresa_id $2
									)RETURNS SETOF pca_courier.vw_tipo_servicio_oficina AS
$$
DECLARE
	_row pca_courier.vw_tipo_servicio_oficina%ROWTYPE;
BEGIN
	FOR _row IN
		SELECT
			oficina_id,
			empresa_id,
			tipo_servicio_id,
			nombre,
			dias_minimo,
			dias_maximo
		FROM
			pca_courier.vw_tipo_servicio_oficina
		WHERE
			oficina_id = $1 AND
			empresa_id = $2
	LOOP
		RETURN NEXT _row;
	END LOOP;
END;
$$
LANGUAGE plpgsql;

-------------------------------------------------------------------------------------------------------------

CREATE TABLE pca_courier.servicios
(
	servicio_id varchar(2) NOT NULL,
	nombre varchar(50) NOT NULL,
	CONSTRAINT servicio_pk PRIMARY KEY(servicio_id)
);

INSERT INTO pca_courier.servicios VALUES('01','EMBOLSADO');
INSERT INTO pca_courier.servicios VALUES('02','ETIQUETADO');
INSERT INTO pca_courier.servicios VALUES('03','EMBALAJE');
INSERT INTO pca_courier.servicios VALUES('04','EMSOBRADO');

CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_servicio() RETURNS SETOF pca_courier.servicios AS
$$
DECLARE
	_row pca_courier.servicios%ROWTYPE;
BEGIN
	FOR _row IN
		SELECT
			servicio_id,
			nombre
		FROM
			pca_courier.servicios
	LOOP
		RETURN NEXT _row;
	END LOOP;
END;
$$
LANGUAGE plpgsql;

-------------------------------------------------------------------------------------------------------------

CREATE TABLE pca_courier.servicios_oficina
(
	oficina_id varchar(2) NOT NULL,
	empresa_id varchar(2) NOT NULL,
	servicio_id varchar(2) NOT NULL,
	CONSTRAINT servicios_oficina_pk PRIMARY KEY(oficina_id,empresa_id,servicio_id),
	CONSTRAINT servicios_fkey FOREIGN KEY(servicio_id)
		REFERENCES pca_courier.servicios(servicio_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT oficinas_fkey FOREIGN KEY (oficina_id, empresa_id)
		REFERENCES pca.oficinas (oficina_id, empresa_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT
);

CREATE OR REPLACE VIEW pca_courier.vw_servicios_oficina AS
SELECT
	o.oficina_id,
	o.empresa_id,
	o.servicio_id,
	s.nombre
FROM
	pca_courier.servicios_oficina o
INNER JOIN
	pca_courier.servicios s
ON
	(o.servicio_id=s.servicio_id);

CREATE OR REPLACE FUNCTION pca_courier.fn_add_servicio_oficina(varchar,--oficina_id $1
							       varchar,--empresa_id $2
							       varchar --servicio_id $3
							       )RETURNS void AS
$$
BEGIN
	INSERT INTO
		pca_courier.servicios_oficina
	VALUES
		($1,$2,$3);
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_del_servicio_oficina(varchar,--oficina_id $1
							       varchar,--empresa_id $2
							       varchar --servicio_id $3
							       )RETURNS void AS
$$
BEGIN
	DELETE FROM
		pca_courier.servicios_oficina
	WHERE
		oficina_id = $1 AND
		empresa_id = $2 AND
		servicio_id = $3;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_servicios_oficina(varchar,--oficina_id $1
								    varchar --empresa_id $2
								    )RETURNS SETOF pca_courier.vw_servicios_oficina AS
$$
DECLARE
	_row pca_courier.vw_servicios_oficina%ROWTYPE;
BEGIN
	FOR _row IN
		SELECT
			oficina_id,
			empresa_id,
			servicio_id,
			nombre
		FROM
			pca_courier.vw_servicios_oficina
		WHERE
			oficina_id = $1 AND
			empresa_id = $2
	LOOP
		RETURN NEXT _row;
	END LOOP;
END;
$$
LANGUAGE plpgsql;

-------------------------------------------------------------------------------------------------------------

/*
 * Contiene los tipos de descargo el campo grupon puede contener los siguiente valores
 * 00 = ENTREGADO
 * 01 = MOTIVO
 */
CREATE TABLE pca_courier.tipo_descargo
(
	tipo_descargo_id varchar(4) NOT NULL,
	nombre varchar(50) NOT NULL,
	CONSTRAINT tipo_descargo_pk PRIMARY KEY(tipo_descargo_id)  
);

INSERT INTO pca_courier.tipo_descargo VALUES('01','ENTREGADO');
INSERT INTO pca_courier.tipo_descargo VALUES('0101','ENTREGADO');
INSERT INTO pca_courier.tipo_descargo VALUES('0102','BAJO PUERTA');
INSERT INTO pca_courier.tipo_descargo VALUES('02','MOTIVO');
INSERT INTO pca_courier.tipo_descargo VALUES('0201','DIRECCION DEFICIENTE (CALLE)');
INSERT INTO pca_courier.tipo_descargo VALUES('0202','DIRECCION DEFICIENTE (NUMERO)');
INSERT INTO pca_courier.tipo_descargo VALUES('0203','SE MUDO (LA EMPRESA)');
INSERT INTO pca_courier.tipo_descargo VALUES('0204','SE MUDO (CONSIGNADO)');
INSERT INTO pca_courier.tipo_descargo VALUES('0205','RECHAZADO (CONSIGNADO YA NO LABORA)');
INSERT INTO pca_courier.tipo_descargo VALUES('0206','RECHAZADO (CONSIGNADO DESCONOCIDO)');
INSERT INTO pca_courier.tipo_descargo VALUES('0207','AUSENTE');
INSERT INTO pca_courier.tipo_descargo VALUES('0208','OTROS');

CREATE OR REPLACE VIEW pca_courier.vw_tipo_descargo AS
SELECT 
	d.descargo_id,
	td.nombre as descargo,
	d.tipo_descargo_id,
	d.nombre
FROM
	(SELECT 
		td.tipo_descargo_id,
		td.nombre,
		substring(td.tipo_descargo_id FROM 1 FOR 2) as descargo_id
	FROM 
		pca_courier.tipo_descargo td
	WHERE
		length(td.tipo_descargo_id)=4) d
INNER JOIN
	pca_courier.tipo_descargo td
ON
	(d.descargo_id=td.tipo_descargo_id);

-------------------------------------------------------------------------------------------------------------

--Estado en el cual se encuentra el consignado
CREATE TABLE pca_courier.estado_reparto
(
	estado_reparto_id varchar(2) NOT NULL,
	nombre varchar(50) NOT NULL,
	CONSTRAINT estado_reparto_pk PRIMARY KEY(estado_reparto_id)
);

INSERT INTO pca_courier.estado_reparto VALUES('00','RECEPCION');
INSERT INTO pca_courier.estado_reparto VALUES('01','FACTORY');--Area de etiquetado embolsado etc.
INSERT INTO pca_courier.estado_reparto VALUES('02','CLASIFICACION');--Se necesita un proceso de pistoleo 
INSERT INTO pca_courier.estado_reparto VALUES('03','TRANSITO');
INSERT INTO pca_courier.estado_reparto VALUES('04','FINALIZADO');

-------------------------------------------------------------------------------------------------------------

--Estado en el cual se encuentra el manifiesto
CREATE TABLE pca_courier.estado_manifiesto
(
	estado_manifiesto_id varchar(2) NOT NULL,
	nombre varchar(50) NOT NULL,
	CONSTRAINT estado_manifiesto_pk PRIMARY KEY(estado_manifiesto_id)
);

INSERT INTO pca_courier.estado_manifiesto VALUES('00','REGISTRADO');
INSERT INTO pca_courier.estado_manifiesto VALUES('01','TRANSITO');
INSERT INTO pca_courier.estado_manifiesto VALUES('02','FINALIZADO');
INSERT INTO pca_courier.estado_manifiesto VALUES('99','ANULADO');

-------------------------------------------------------------------------------------------------------------

CREATE TABLE pca_courier.destinos
(
	destino_id varchar(2) NOT NULL,
	nombre varchar(50) NOT NULL,
	CONSTRAINT destinos_pk PRIMARY KEY(destino_id)
);

CREATE OR REPLACE FUNCTION pca_courier.fn_add_destino(varchar,--destino_id $1
						      varchar --nombre $2
						      )RETURNS void AS
$$
BEGIN
	INSERT INTO
		pca_courier.destinos
	VALUES
		($1,$2);
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_update_destino(varchar,--destino_id $1
						         varchar --nombre $2
						         )RETURNS void AS
$$
BEGIN
	UPDATE pca_courier.destinos SET
		nombre =$2
	WHERE
		destino_id = $1;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_del_destino(varchar --destino_id $1
						      )RETURNS void AS
$$
BEGIN
	DELETE FROM
		pca_courier.destinos
	WHERE
		destino_id = $1;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_destino()RETURNS SETOF pca_courier.destinos AS
$$
DECLARE
	_row pca_courier.destinos%ROWTYPE;
BEGIN
	FOR _row IN
		SELECT
			destino_id,
			nombre
		FROM
			pca_courier.destinos
	LOOP
		RETURN NEXT _row;
	END LOOP;
END;
$$
LANGUAGE plpgsql;

-------------------------------------------------------------------------------------------------------------

CREATE TABLE pca_courier.destinos_oficina
(
	oficina_id varchar(2) NOT NULL,
	empresa_id varchar(2) NOT NULL,
	destino_id varchar(2) NOT NULL,
	CONSTRAINT destinos_oficina_pk PRIMARY KEY(oficina_id,empresa_id,destino_id),
	CONSTRAINT destinos_fkey FOREIGN KEY(destino_id)
		REFERENCES pca_courier.destinos(destino_id)MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT oficinas_fkey FOREIGN KEY (oficina_id, empresa_id)
		REFERENCES pca.oficinas (oficina_id, empresa_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT
);

CREATE OR REPLACE VIEW pca_courier.vw_destinos_oficina AS
SELECT
	o.oficina_id,
	o.empresa_id,
	o.destino_id,
	d.nombre
FROM
	pca_courier.destinos_oficina o
INNER JOIN
	pca_courier.destinos d
ON
	(o.destino_id=d.destino_id);

CREATE OR REPLACE FUNCTION pca_courier.fn_add_destino_oficina(varchar,--oficina_id $1
							      varchar,--empresa_id $2
							      varchar --destino_id $3
							      )RETURNS void AS
$$
BEGIN
	INSERT INTO
		pca_courier.destinos_oficina
	VALUES
		($1,$2,$3);
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_del_destino_oficina(varchar,--oficina_id $1
							      varchar,--empresa_id $2
							      varchar --destino_id $3
							      )RETURNS void AS
$$
BEGIN
	DELETE FROM
		pca_courier.destinos_oficina
	WHERE
		oficina_id = $1 AND
		empresa_id = $2 AND
		destino_id = $3;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_destinos_oficina(varchar,--oficina_id $1
								    varchar --empresa_id $2
								    )RETURNS SETOF pca_courier.vw_destinos_oficina AS
$$
DECLARE
	_row pca_courier.vw_destinos_oficina%ROWTYPE;
BEGIN
	FOR _row IN
		SELECT
			oficina_id,
			empresa_id,
			destino_id,
			nombre
		FROM
			pca_courier.vw_destinos_oficina
		WHERE
			oficina_id = $1 AND
			empresa_id = $2
	LOOP
		RETURN NEXT _row;
	END LOOP;
END;
$$
LANGUAGE plpgsql;